package edu.egypt.helpers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import edu.egypt.model.Permission;
import edu.egypt.model.Role;

public class RoleDbOperations {
	private static RoleDbOperations instance = null;
	private static Connection conn= null;
	
	private RoleDbOperations(){
		// making only constructor as private so that no one can get instance other than getInstance method
	}
	
	public static synchronized RoleDbOperations getSingletonInstance(){
		
		if( instance == null){
			instance = new RoleDbOperations();
		}
		
		return instance;
		
	}
	
	public ArrayList<Role> getAllRoles(){
		
		ArrayList<Role> result = new ArrayList<>();
		try{
			Statement stmt = getDbConnection().createStatement();
			String Query = "SELECT * From role";
			ResultSet rs = stmt.executeQuery(Query);
			
			while(rs.next()){
				Role role = new Role(rs.getInt("ID"),rs.getString("Name"),rs.getString("Description"));
				result.add(role);
			}
			// get all permissions as resultset and then convert them into arrayist of permissions..
		}catch(SQLException ex){
			
		}
		return result;
		
	}
	
	public void UpdateRolePerm(ArrayList<Permission> N , int roleId){
		
		try{
			
			for(int i = 0 ; i < N.size() ; i++){
					if(N.get(i).getAssigned()){
					PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("INSERT IGNORE INTO role_permission (Role_ID,Permission_ID) VALUES(?,?) ");
					st.setInt(1, roleId);
					st.setInt(2,N.get(i).getId());
					st.execute();
					}else{
						PreparedStatement stDelete = (PreparedStatement)getDbConnection().prepareStatement("DELETE FROM role_permission WHERE Role_ID = ? AND Permission_ID = ? ");
						stDelete.setInt(1, roleId);
						stDelete.setInt(2,N.get(i).getId());
						stDelete.execute();	
					}
				
			}
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}
	}
	
	public Role getRoleById(int id){
		Role role = new Role();
		try{
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("SELECT * FROM role where id = ?");
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();
			while(rs.next()){
			 role.setId(rs.getInt("ID"));
			 role.setName(rs.getString("Name"));
			 role.setDescription(rs.getString("Description"));
			}
		}catch(SQLException ex){
			ex.printStackTrace();
		}
		return role;
	}
	
	
	private Connection getDbConnection(){
		
		if ( conn == null){
			
			try{
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection("jdbc:mysql://localhost/ibm", "root", "");
			}catch(ClassNotFoundException | SQLException ex){
				ex.printStackTrace();
			}
		}
		
		return conn;
	}

	public void editRole(int id, String name , String desc){
		try{
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("UPDATE role set Name = ?, Description = ? WHERE ID = ? ");
			
			st.setString(1, name);
			st.setString(2, desc);
			st.setInt(3, id);
			
			st.executeUpdate();
		}catch(SQLException ex){
			ex.printStackTrace();
		}
	}
	
	public void addRole(String name , String desc){
		
		try{
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("INSERT INTO role (Name,Description) VALUES(?,?) ");
			st.setString(1, name);
			st.setString(2, desc);
			
			st.execute();
		}catch(SQLException ex){
			ex.printStackTrace();
		}
	}
	
	public void deleteRole(int id){
		
		try{
			
			PreparedStatement st = (PreparedStatement)getDbConnection().prepareStatement("DELETE FROM role WHERE ID = ? ");
			st.setInt(1,id);
			
			st.execute();
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}
	}

}
